package com.guestmanage.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.guestmanage.bean.ChannelArgs;
import com.guestmanage.bean.ChannelRecordVO;
import com.guestmanage.bean.RdgionYu2;
import com.spmanage.util.DbOperation;
/**
 * 通道信息查询
 * @author Jenno.song
 * 2011-03-07
 *
 */
public class ChannelRecordDao {
	/**
	 * 查询地区
	 * @param args参数条件
	 * @param sql
	 * @return
	 */
	private RdgionYu2 queryForList(String args,String sql){
		RdgionYu2 rd =new RdgionYu2();
		DbOperation db=new DbOperation(DbOperation.DB_USER);
		if(!db.prepareStatement(sql)){
			db.release();
			return null;
		}
		PreparedStatement pstmt=db.getPStmt();
		try {
			if(args!=null){
				pstmt.setString(1, args);
				pstmt.setString(2, args.substring(1, args.length()));
			}
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				rd=getRdgionYu2(rs);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.release();
		}
		return rd;
	}
	
	private RdgionYu2 getRdgionYu2(ResultSet rs) throws SQLException{
		RdgionYu2 rd=new RdgionYu2();
		rd.setAreaid(rs.getString("areaid"));
		rd.setCity(rs.getString("city"));
		rd.setCode(rs.getString("code"));
		rd.setPhnid(rs.getString("phnid"));
		rd.setSheng(rs.getString("sheng"));
		rd.setTeleid(rs.getString("teleid"));
		rd.setType(rs.getString("type"));
		rd.setTypeid(rs.getInt("typeid"));
		return rd;
	}
	/**
	 * 根据地区编号查询
	 * @param teleid
	 * @return
	 */
	private RdgionYu2 getRdgionYu2ByTeleid(String teleid){
		String sql="select * from REGION_YU2 where teleid in (?,?)";
		return queryForList(teleid,sql);
	}
	/**
	 * 根据地区名称查询
	 * @param city
	 * @return
	 */
	private RdgionYu2 getRdgionYu2ByCity(String city){
		String sql="select * from REGION_YU2 where city like '%"+city+"%' ";
		return queryForList(null,sql);
	}
	/**
	 * 根据地区查询对象
	 * @param args条件地区编号或者名称
	 * @param type 0代表返回地区编号，其它代表返回地名
	 * @return
	 */
	private String getRdgionYu2(String args,int type){
		if(args.getBytes().length==args.length()){
			if(type==0)
				return getRdgionYu2ByTeleid(args).getTeleid();
			else
				return getRdgionYu2ByTeleid(args).getCity();
		}
		else{
			if(type==0){
				return getRdgionYu2ByCity(args).getTeleid();
			}else{
				return getRdgionYu2ByCity(args).getCity();
			}
		}
	}
	/**
	 * 获取总数
	 * @param sql
	 * @param args 条件查询
	 * @return
	 */
	private int getCount(String sql,ChannelArgs args){
		int count=0;
		System.out.println(sql);
		DbOperation db = new DbOperation(DbOperation.DB_BAK_SP);
		if(!db.prepareStatement(sql)){
			db.release();
			return 0;
		}
		PreparedStatement pstmt=db.getPStmt();
		try {
			if(args!=null){
				int sqlPra=sql.split("\\?").length-1;//获取sql语句中?号的个数
				for(int i=0;i<sqlPra;i++){
					if(args.getTimerStr()>0){
						if(args.getFrom()!=null&&!args.getFrom().equals("")){
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":00:00");
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":59:59");
						}
					}else{
						if(args.getFrom()!=null&&!args.getFrom().equals("")){
							pstmt.setString(++i, args.getFrom()+" 00:00:00");
							pstmt.setString(++i, args.getFrom()+" 23:59:59");
						}
					}
					if(args.getBody()!=null&&!args.getBody().equals("")){
						pstmt.setString(++i, args.getBody());
					}
					if(args.getStype()!=null&&!args.getStype().equals("")){
						pstmt.setString(++i, args.getStype());
					}
					if(args.getCity()!=null&&!args.getCity().equals("")){
						String rd=getRdgionYu2(args.getCity(),0);
						pstmt.setString(++i, rd);
					}
				}
			}
			ResultSet rs=pstmt.executeQuery();
			if(rs.next()){
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.release();
		}
			return count;
	}
	
	/**
	 * 查询月表总数
	 * @param args
	 * @return
	 */
	public int getCountMonths(ChannelArgs args){
		String sql="select count(*) from recv"+tableDate(args.getFrom())+" a,send"+tableDate(args.getFrom())+" b " +
		"where a.linkid=b.linkid and a.linkid!=''  and a.tm>=? and a.tm<=? ";
if(args!=null){
	if(args.getBody()!=null&&!args.getBody().equals("")){
		sql+="and a.body=? ";
	}
	if(args.getDesc()!=null&&!args.getDesc().equals("")){
		sql+="and a.dest like '%"+args.getDesc()+"%' ";
	}
	if(args.getStype()!=null&&!args.getStype().equals("")){
		sql+="and b.stype=? ";
	}
	if(args.getCity()!=null&&!args.getCity().equals("")){
		sql+="and (substring(a.org,1,7) in(select phnid from db_user..REGION_YU2 where TELEID=?)) ";
	}
}
return getCount(sql, args);
	}
	/**
	 * 查询日表总数
	 * @param args
	 * @return
	 */
	public int getCountDays(ChannelArgs args){
		String sql="select count(*) from "+getRecvDayTable()+" a,"+getSendDayTable()+" b " +
		"where a.linkid=b.linkid and a.linkid!=''   and a.tm>=? and a.tm<=? ";
if(args!=null){
	if(args.getBody()!=null&&!args.getBody().equals("")){
		sql+="and a.body=? ";
	}
	if(args.getDesc()!=null&&!args.getDesc().equals("")){
		sql+="and a.dest like '%"+args.getDesc()+"%' ";
	}
	if(args.getStype()!=null&&!args.getStype().equals("")){
		sql+="and b.stype=? ";
	}
	if(args.getCity()!=null&&!args.getCity().equals("")){
		sql+="and (substring(a.org,1,7) in(select phnid from db_user..REGION_YU2 where TELEID=?)) ";
	}
}
return getCount(sql, args);
	}
	/**
	 * 分页查询月表信息
	 * @param args
	 * @param pageSize
	 * @param pageNow
	 * @return
	 */
	public List<ChannelRecordVO> queryCRVOMonths(ChannelArgs args,int pageSize,int pageNow){
		 String sql="select top " +pageSize+
			" a.org as phoneNum," +
			"a.dest as port," +
			"b.stype as spCode," +
			"a.body as mo," +
			"b.body as mt," +
			"b.feecode as infoMoney," +
			"b.stat as status," +
			"a.tm as crTime," +
			"a.teleid as city "+
			"from recv" +
			tableDate(args.getFrom())+" a, send"+tableDate(args.getFrom())+" b ";
	String sqlorder="order by a.tm desc";
	String where=" a.linkid=b.linkid and a.linkid!='' ";
	String notin="where a.autoid not in (select top "+pageNow+" a.autoid from  recv"+tableDate(args.getFrom())+" a, send"+tableDate(args.getFrom())+" b where ";
	String notin2=" a.tm>=? and a.tm<=? and ";
	String notin3=" ) and ";
	if(args!=null){
		if(args.getBody()!=null&&!args.getBody().equals("")){
			where+="and a.body like '%"+args.getBody()+"%' ";
		}
		if(args.getDesc()!=null&&!args.getDesc().equals("")){
			where+="and a.dest like '%"+args.getDesc()+"%' ";
		}
		if(args.getStype()!=null&&!args.getStype().equals("")){
			where+="and b.stype=? ";
		}
		if(args.getCity()!=null&&!args.getCity().equals("")){
			where+="and (substring(a.org,1,7) in(select phnid from db_user..REGION_YU2 where TELEID=?)) ";
		}
	}
	String queryString=sql+notin+notin2+where+sqlorder+notin3+where+" and "+notin2+sqlorder;
	queryString=queryString.substring(0, queryString.lastIndexOf("and"))+queryString.substring(queryString.lastIndexOf("and")+3,queryString.length());
	return queryForCRVO(queryString,args,pageSize,pageNow);
	}
	/**
	 * 分页查询日表信息
	 * @param args
	 * @param pageSize
	 * @param pageNow
	 * @return
	 */
	public List<ChannelRecordVO> queryForCRVODays(ChannelArgs args,int pageSize,int pageNow){
		String sql="select top " +pageSize+
		" a.org as phoneNum," +
		"a.dest as port," +
		"b.stype as spCode," +
		"a.body as mo," +
		"b.body as mt," +
		"b.feecode as infoMoney," +
		"b.stat as status," +
		"a.tm as crTime," +
		"a.teleid as city "+
		"from " +
		getRecvDayTable()+" a,"+getSendDayTable()+" b ";
String sqlorder="order by a.tm desc";
String where=" a.linkid=b.linkid and a.linkid!='' ";
String notin="where a.autoid not in (select top "+pageNow+" a.autoid from  "+getRecvDayTable()+" a,"+getSendDayTable()+" b where ";
String notin2=" a.tm>=? and a.tm<=? and ";
String notin3=" ) and ";
if(args!=null){
	if(args.getBody()!=null&&!args.getBody().equals("")){
		where+="and a.body like '%"+args.getBody()+"%' ";
	}
	if(args.getDesc()!=null&&!args.getDesc().equals("")){
		where+="and a.dest like '%"+args.getDesc()+"%' ";
	}
	if(args.getStype()!=null&&!args.getStype().equals("")){
		where+="and b.stype=? ";
	}
	if(args.getCity()!=null&&!args.getCity().equals("")){
		where+="and (substring(a.org,1,7) in(select phnid from db_user..REGION_YU2 where TELEID=?)) ";
	}
}
String queryString=sql+notin+notin2+where+sqlorder+notin3+where+" and "+notin2+sqlorder;
queryString=queryString.substring(0, queryString.lastIndexOf("and"))+queryString.substring(queryString.lastIndexOf("and")+3,queryString.length());
return queryForCRVO(queryString,args,pageSize,pageNow);
	}
	
	/**
	 * 通道记录查询
	 * @param args
	 * @return
	 */
	private List<ChannelRecordVO> queryForCRVO(String sql,ChannelArgs args,int pageSize,int pageNow){
		List<ChannelRecordVO> crvo_list=new ArrayList<ChannelRecordVO>();
		DbOperation db = new DbOperation(DbOperation.DB_BAK_SP);
		System.out.println(sql);
		if(!db.prepareStatement(sql)){
			db.release();
			return null;
		}
		PreparedStatement pstmt=db.getPStmt();
		try {
			if(args!=null){
				int sqlPra=(sql).split("\\?").length-1;//获取sql语句中?号的个数
				for(int i=0;i<sqlPra;i++){
					if(args.getTimerStr()>0){
						if(args.getFrom()!=null&&!args.getFrom().equals("")){
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":00:00");
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":59:59");
//							if(args.getBody()!=null&&!args.getBody().equals("")){
//								pstmt.setString(++i, args.getBody());
//							}
							if(args.getStype()!=null&&!args.getStype().equals("")){
								pstmt.setString(++i, args.getStype());
							}
							if(args.getCity()!=null&&!args.getCity().equals("")){
								String rd=getRdgionYu2(args.getCity(),0);
								pstmt.setString(++i, rd);
							}
							
//							if(args.getBody()!=null&&!args.getBody().equals("")){
//								pstmt.setString(++i, args.getBody());
//							}
							if(args.getStype()!=null&&!args.getStype().equals("")){
								pstmt.setString(++i, args.getStype());
							}
							if(args.getCity()!=null&&!args.getCity().equals("")){
								String rd=getRdgionYu2(args.getCity(),0);
								pstmt.setString(++i, rd);
							}
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":00:00");
							pstmt.setString(++i, args.getFrom()+" "+args.getTimerStr()+":59:59");
						}
					}else{
						if(args.getFrom()!=null&&!args.getFrom().equals("")){
							pstmt.setString(++i, args.getFrom()+" 00:00:00");
							pstmt.setString(++i, args.getFrom()+" 23:59:59");
//							if(args.getBody()!=null&&!args.getBody().equals("")){
//								pstmt.setString(++i, args.getBody());
//							}
							if(args.getStype()!=null&&!args.getStype().equals("")){
								pstmt.setString(++i, args.getStype());
							}
							if(args.getCity()!=null&&!args.getCity().equals("")){
								String rd=getRdgionYu2(args.getCity(),0);
								pstmt.setString(++i, rd);
							}
							
//							if(args.getBody()!=null&&!args.getBody().equals("")){
//								pstmt.setString(++i, args.getBody());
//							}
							if(args.getStype()!=null&&!args.getStype().equals("")){
								pstmt.setString(++i, args.getStype());
							}
							if(args.getCity()!=null&&!args.getCity().equals("")){
								String rd=getRdgionYu2(args.getCity(),0);
								pstmt.setString(++i, rd);
							}
							pstmt.setString(++i, args.getFrom()+" 00:00:00");
							pstmt.setString(++i, args.getFrom()+" 23:59:59");
						}
					}
				}
			}
			ResultSet rs=pstmt.executeQuery();
			while(rs.next()){
				ChannelRecordVO crvo=getChannelRecordVO(rs);
				crvo_list.add(crvo);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.release();
		}
		return crvo_list;
	}
	
	private ChannelRecordVO getChannelRecordVO(ResultSet rs) throws SQLException{
		ChannelRecordVO crvo=new ChannelRecordVO();
		crvo.setCity(getRdgionYu2(rs.getString("city"),1));
		crvo.setCrTime(rs.getString("crTime"));
		crvo.setInfoMoney(rs.getString("infoMoney"));
		crvo.setMo(rs.getString("mo"));
		crvo.setMt(rs.getString("mt"));
		crvo.setPhoneNum(rs.getString("phoneNum"));
		crvo.setPort(rs.getString("port"));
		crvo.setSpCode(rs.getString("spCode"));
		crvo.setStatus(rs.getString("status"));
		return crvo;
	}
	/**
	 * 获取日表上行 表名
	 * @return
	 */
	private String getRecvDayTable(){
		return "recv_"+getTable("yyMMdd");
	}
	/**
	 * 日表下行表名
	 * @return
	 */
	private String getSendDayTable(){
		return "send_"+getTable("yyMMdd");
	}
	
	private String getTable(String type){
		SimpleDateFormat format=new SimpleDateFormat(type);
		Date date=new Date();
		return format.format(date);
	}
	
	public String tableDate(String str){
		return "_"+str.substring(2, 4)+str.substring(5, 7);
	}
}
